package it.omnisong.server.persistence.dao;

import it.omnisong.client.model.Link;
import it.omnisong.client.model.Song;
import it.omnisong.server.persistence.postgres.DataBaseManager;
import it.omnisong.server.persistence.postgres.PostgresException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class LinkDAO {

	private Connection connection;
	
	public LinkDAO() throws PostgresException {
		try {
			connection = DataBaseManager.getConnection();
			
		} catch (SQLException e) {
			throw new PostgresException(e.getMessage());
		}		
	}
	
	public int insertLink(Link link) throws PostgresException {
		
		SongDAO songDAO = new SongDAO();
		
		int linkId = -1;
		try {
			linkId = getNextId();
			int songId = songDAO.insertSong(link.getSong());
		
			PreparedStatement psti = connection.prepareStatement("INSERT INTO link (id, url, song, rank) VALUES (?, ?, ?, ?)");
			psti.setInt(1, linkId);
			psti.setString(2, link.getUrl());
			psti.setInt(3, songId);
			psti.setInt(4, link.getRank());
			psti.executeUpdate();
			psti.close();
		
		} catch (SQLException e) {
			throw new PostgresException(e.getMessage());
		}
		return linkId;
	}
	
	public int insertLink(Link link, Song song) throws PostgresException {
		
		int linkId = -1;
		try {
			linkId = getNextId();
			int songId = song.getId();
		
			PreparedStatement psti = connection.prepareStatement("INSERT INTO link (id, url, song, rank) VALUES (?, ?, ?, ?)");
			psti.setInt(1, linkId);
			psti.setString(2, link.getUrl());
			psti.setInt(3, songId);
			psti.setInt(4, link.getRank());
			psti.executeUpdate();
			psti.close();
			
		} catch (SQLException e) {
			throw new PostgresException(e.getMessage());
		}
		return linkId;
	}
	
	public int getNextId() throws PostgresException {
		Statement stid;
		int linkId = -1;
		try {
		
			stid = connection.createStatement();
			ResultSet rsid = stid.executeQuery("SELECT nextval('link_id_seq')");
			rsid.next();
			linkId = rsid.getInt(1);
			rsid.close();
			stid.close();
	
		} catch (SQLException e) {
			throw new PostgresException(e.getMessage());
		}

		return linkId;
	}
	
	public Link get(int id) throws PostgresException {
		
		PreparedStatement psts;
		Link link = null;
		
		try {
			
			psts = connection.prepareStatement("SELECT * from link where id=?");
			psts.setInt(1, id);
			ResultSet rs = psts.executeQuery();
			rs.next();
		
			link = new Link();
			link.setId(rs.getInt("id"));
			link.setUrl(rs.getString("url"));
			link.setSong(new Song(rs.getInt("song")));
			link.setRank(rs.getInt("rank"));
		
			rs.close();
			psts.close();
			System.out.println("Selezionato Link: " + link);
		
		} catch (SQLException e) {
			throw new PostgresException(e.getMessage());
		}
		
		return link;
	}
	
	public List<Link> get(String url) throws PostgresException {
		
		List<Link> links = new ArrayList<Link>();
		PreparedStatement psts;
		
		try {
			
			psts = connection.prepareStatement("SELECT * from link where url=?");
			psts.setString(1, url);
			ResultSet rs = psts.executeQuery();
			while(rs.next()) {
				
				Link link = new Link();
				link.setId(rs.getInt("id"));
				link.setUrl(rs.getString("url"));
				link.setSong(new Song(rs.getInt("song")));
				link.setRank(rs.getInt("rank"));
			
				System.out.println("Selezionato Link: " + link);
				links.add(link);
				
			}

			rs.close();
			psts.close();
		
		} catch (SQLException e) {
			throw new PostgresException(e.getMessage());
		}
		
		return links;
	}
}
